-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Insert]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Update]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Delete]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Load]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_ChungTu_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectAll]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Insert]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@PhienBan int,
	@SoTo int,
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@TrangThai varchar(8),
	@ViTriLuuTru varchar(32),
	@GhiChu nvarchar(1024),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS
INSERT INTO [dbo].[t_HAIQUAN_QLHS_ChungTu]
(
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[PhienBan],
	[SoTo],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[TrangThai],
	[ViTriLuuTru],
	[GhiChu],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
)
VALUES
(
	@SoToKhai,
	@MaHaiQuan,
	@MaLoaiHinh,
	@NamDangKy,
	@PhienBan,
	@SoTo,
	@NoiGiao,
	@NguoiGiao,
	@NoiNhan,
	@NguoiNhan,
	@NgayBanGiao,
	@TrangThai,
	@ViTriLuuTru,
	@GhiChu,
	@NguoiNhapDuLieu,
	@NgayNhapDuLieu
)

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Update]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@PhienBan int,
	@SoTo int,
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@TrangThai varchar(8),
	@ViTriLuuTru varchar(32),
	@GhiChu nvarchar(1024),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS

UPDATE
	[dbo].[t_HAIQUAN_QLHS_ChungTu]
SET
	[SoTo] = @SoTo,
	[NoiGiao] = @NoiGiao,
	[NguoiGiao] = @NguoiGiao,
	[NoiNhan] = @NoiNhan,
	[NguoiNhan] = @NguoiNhan,
	[NgayBanGiao] = @NgayBanGiao,
	[TrangThai] = @TrangThai,
	[ViTriLuuTru] = @ViTriLuuTru,
	[GhiChu] = @GhiChu,
	[NguoiNhapDuLieu] = @NguoiNhapDuLieu,
	[NgayNhapDuLieu] = @NgayNhapDuLieu
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [PhienBan] = @PhienBan

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_InsertUpdate]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@PhienBan int,
	@SoTo int,
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@TrangThai varchar(8),
	@ViTriLuuTru varchar(32),
	@GhiChu nvarchar(1024),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS
IF EXISTS(SELECT [SoToKhai], [MaHaiQuan], [MaLoaiHinh], [NamDangKy], [PhienBan] FROM [dbo].[t_HAIQUAN_QLHS_ChungTu] WHERE [SoToKhai] = @SoToKhai AND [MaHaiQuan] = @MaHaiQuan AND [MaLoaiHinh] = @MaLoaiHinh AND [NamDangKy] = @NamDangKy AND [PhienBan] = @PhienBan)
	BEGIN
		UPDATE
			[dbo].[t_HAIQUAN_QLHS_ChungTu] 
		SET
			[SoTo] = @SoTo,
			[NoiGiao] = @NoiGiao,
			[NguoiGiao] = @NguoiGiao,
			[NoiNhan] = @NoiNhan,
			[NguoiNhan] = @NguoiNhan,
			[NgayBanGiao] = @NgayBanGiao,
			[TrangThai] = @TrangThai,
			[ViTriLuuTru] = @ViTriLuuTru,
			[GhiChu] = @GhiChu,
			[NguoiNhapDuLieu] = @NguoiNhapDuLieu,
			[NgayNhapDuLieu] = @NgayNhapDuLieu
		WHERE
			[SoToKhai] = @SoToKhai
			AND [MaHaiQuan] = @MaHaiQuan
			AND [MaLoaiHinh] = @MaLoaiHinh
			AND [NamDangKy] = @NamDangKy
			AND [PhienBan] = @PhienBan
	END
ELSE
	BEGIN
	INSERT INTO [dbo].[t_HAIQUAN_QLHS_ChungTu]
	(
			[SoToKhai],
			[MaHaiQuan],
			[MaLoaiHinh],
			[NamDangKy],
			[PhienBan],
			[SoTo],
			[NoiGiao],
			[NguoiGiao],
			[NoiNhan],
			[NguoiNhan],
			[NgayBanGiao],
			[TrangThai],
			[ViTriLuuTru],
			[GhiChu],
			[NguoiNhapDuLieu],
			[NgayNhapDuLieu]
	)
	VALUES
	(
			@SoToKhai,
			@MaHaiQuan,
			@MaLoaiHinh,
			@NamDangKy,
			@PhienBan,
			@SoTo,
			@NoiGiao,
			@NguoiGiao,
			@NoiNhan,
			@NguoiNhan,
			@NgayBanGiao,
			@TrangThai,
			@ViTriLuuTru,
			@GhiChu,
			@NguoiNhapDuLieu,
			@NgayNhapDuLieu
	)	
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Delete]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@PhienBan int
AS

DELETE FROM 
	[dbo].[t_HAIQUAN_QLHS_ChungTu]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [PhienBan] = @PhienBan

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_DeleteDynamic]
	@WhereCondition nvarchar(500)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = 'DELETE FROM [dbo].[t_HAIQUAN_QLHS_ChungTu] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_Load]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@PhienBan int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[PhienBan],
	[SoTo],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[TrangThai],
	[ViTriLuuTru],
	[GhiChu],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM
	[dbo].[t_HAIQUAN_QLHS_ChungTu]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [PhienBan] = @PhienBan
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = 
'SELECT 
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[PhienBan],
	[SoTo],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[TrangThai],
	[ViTriLuuTru],
	[GhiChu],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM [dbo].[t_HAIQUAN_QLHS_ChungTu] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_ChungTu_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[PhienBan],
	[SoTo],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[TrangThai],
	[ViTriLuuTru],
	[GhiChu],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM
	[dbo].[t_HAIQUAN_QLHS_ChungTu]	

GO
